import java.sql.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import javax.servlet.ServletContextListener;
import javax.servlet.ServletContextEvent;

public class initialdb implements ServletContextListener {
    public void contextInitialized(ServletContextEvent evt){
	String URL=evt.getServletContext().getInitParameter("db.url");
	String NAME=evt.getServletContext().getInitParameter("db.user");
	String PASSWORD=evt.getServletContext().getInitParameter("db.password");
	System.out.println(URL+NAME+PASSWORD);
	String webappPath=evt.getServletContext().getRealPath("/");

	try (Connection conn = DriverManager.getConnection(URL, NAME, PASSWORD)) {
	    Statement stat = conn.createStatement();
	    stat.execute(" CREATE TABLE `user` ( `username` varchar(20) NOT NULL, `passwd` varchar(20) NOT NULL, `familyname` char(4) NOT NULL, `name` char(4) NOT NULL, `email` varchar(64) NULL, PRIMARY KEY (`username`));");
	    stat.execute(" CREATE TABLE `image` ( `username` varchar(20) NOT NULL, `portrait` MEDIUMBLOB NOT NULL, PRIMARY KEY (`username`));");

	    /*
	     * CREATE TABLE `IMAGE` (
	     * `username` varchar(20) NOT NULL
	     * `portrait` MEDIUMLOB NOT NULL
	     * );
	     * */

	    System.out.println("start test");
	    stat.execute(" insert into `user` (`username`,`passwd`,`familyname`,`name`,`email`) values ('admin','12345', '腊','鸡','886@awsl.com' );");
	    
	    File defaultPortrait = new File(webappPath+"/src/default.jpg");
	    FileInputStream inputStream = new FileInputStream(defaultPortrait);
	    PreparedStatement pstat = conn.prepareStatement("insert into `image`(`username`,`portrait`) values ('admin',?)");
	    pstat.setBinaryStream(1,inputStream,defaultPortrait.length());
	    pstat.execute();

	    ResultSet rs=stat.executeQuery("select * from user where username='admin'");
	    rs.next();
	    System.out.println(rs.getString("username")+rs.getString("passwd"));
	}catch (SQLException |FileNotFoundException e){
	    e.printStackTrace();
	}
    }
}





